package com.jinxin.platform.cdfaceacs.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.springframework.web.multipart.MultipartFile;
import sun.misc.BASE64Encoder;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.util.*;

/**
 * @Author: chenyinlong
 * @Description:
 * @Date: Create in 10:47 2019/9/6
 */
@Slf4j
public class POIUtil {
    private final static String xls = "xls";
    private final static String xlsx = "xlsx";


    public static List<String[]> readExcel(MultipartFile file, int lastCellNum) throws IOException {
        int sheetNum = 0;
//        int lastCellNum = 0;
//        switch (index){
//            case 0:
//                sheetNum = 0;
//                lastCellNum = 6;
//                break;
//            case 1:
//                sheetNum = 0;
//                lastCellNum = 10;
//                break;
//            case 2:
//                sheetNum = 0;
//                lastCellNum = 10;
//                break;
//        }
        //检查文件
        checkFile(file);
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(file);
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<String[]> list = new ArrayList<>();
        if(workbook != null){
            //获得当前sheet工作表
            Sheet sheet = workbook.getSheetAt(sheetNum);
            //获得当前sheet的开始行
            int firstRowNum  = sheet.getFirstRowNum();
            //获得当前sheet的结束行
            int lastRowNum = sheet.getLastRowNum();


            //循环除了第一行的所有行
            for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
                //获得当前行
                Row row = sheet.getRow(rowNum);
                if(row == null){
                    continue;
                }

                //获得当前行的开始列
                int firstCellNum = 0;
                //获得当前行的列数
                String[] cells = new String[lastCellNum];
                // 用户表读取图片
                Map<String, PictureData> dataMap = null;
                if(lastCellNum==13){
                    String fileName = file.getOriginalFilename();
                    try {
                        if (fileName.endsWith(xls)) {
                            dataMap = getXlsPictures((HSSFSheet) sheet);
                        } else if (fileName.endsWith(xlsx)) {
                            dataMap = getXlsxPictures((XSSFSheet) sheet);
                        }

                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                //循环当前行
                for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
                    // 用户表 最后一列是人脸图片
                    if(lastCellNum == 13  && cellNum == lastCellNum-1 ) {
                        PictureData pictureData = dataMap != null ? dataMap.get(rowNum + "-"+cellNum) : null;
                        // 获取图片的格式
                        if(null != pictureData){
//                            String ext = pictureData.suggestFileExtension();
                            BASE64Encoder encoder = new BASE64Encoder();
                            String base64PicStr = encoder.encode(pictureData.getData());
                            cells[cellNum] = base64PicStr;
                        }
                    }else{
                        Cell cell = row.getCell(cellNum);
                        cells[cellNum] = getCellValue(cell);
                    }
                }
                list.add(cells);
            }
            workbook.close();
        }
        return list;
    }

    public static List<String[]> parseExcel(MultipartFile file, int sheetNum) throws IOException {
        //检查文件
        checkFile(file);
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(file);
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<String[]> list = new ArrayList<>();
        if(workbook != null){
            //获得当前sheet工作表
            Sheet sheet = workbook.getSheetAt(sheetNum);
            //获得当前sheet的开始行
            int firstRowNum  = sheet.getFirstRowNum();
            //获得当前sheet的结束行
            int lastRowNum = sheet.getLastRowNum();
            //循环除了第一行的所有行
            short cellNums = 0;
            for(int rowNum = firstRowNum;rowNum <= lastRowNum;rowNum++){
                //获得当前行
                Row row = sheet.getRow(rowNum);
                // 获取列数
                if(rowNum == firstRowNum){
                    cellNums = row.getLastCellNum();//获取一行的有效单元格个数
                }
                if(row == null){
                    continue;
                }
                //获得当前行的开始列
                int firstCellNum = 0;
                //获得当前行的列数
                String[] cells = new String[cellNums];
                //循环当前行
                for(int cellNum = firstCellNum; cellNum < cellNums;cellNum++){
                    Cell cell = row.getCell(cellNum);
                    cells[cellNum] = getCellValue(cell);
                }
                list.add(cells);
            }
            workbook.close();
        }
        return list;
    }

    /**
     * 获取图片和位置 (xls)
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, PictureData> getXlsPictures (HSSFSheet sheet) throws IOException {
        Map<String, PictureData> map = new HashMap<>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                PictureData pdata = picture.getPictureData();
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号
                map.put(key, pdata);
            }
        }
        return map;
    }
    /**
     * 获取图片和位置 (xlsx)
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, PictureData> getXlsxPictures (XSSFSheet sheet) throws IOException {
        Map<String, PictureData> map = new HashMap<>();
        List<POIXMLDocumentPart> list = sheet.getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
                    String key = marker.getRow() + "-" + marker.getCol();
                    map.put(key, picture.getPictureData());
                }
            }
        }
        return map;
    }

    public static void checkFile(MultipartFile file) throws IOException{
        //判断文件是否存在
        if(null == file){
            log.info("文件不存在！");
            throw new FileNotFoundException("文件不存在！");
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
            log.info(fileName + "不是excel文件");
            throw new IOException(fileName + "不是excel文件");
        }
    }
    public static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if(fileName.endsWith(xls)){
                //2003
                workbook = new HSSFWorkbook(is);
            }else if(fileName.endsWith(xlsx)){
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            log.error("error",e);
        }
        return workbook;
    }
    public static String getCellValue(Cell cell){
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if(cell.getCellType() == CellType.NUMERIC){
            cell.setCellType(CellType.STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()){
            case NUMERIC: //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: //空值
                cellValue = "";
                break;
            case ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
//    public static String showContentByType(GuideDocumentDo doc, String access_dir, HttpServletResponse response) {
//        StringBuilder content = new StringBuilder();
//        if (doc == null || StringUtils.isEmpty(doc.getMemo())) { //存储文档路径
//            return null;
//        }
//        String path = doc.getMemo();
//        String realPath = PathUtil.getRealPath();
//        String picName = path.substring(path.lastIndexOf("/") + 1);
//        String filepath = realPath + access_dir + picName;
//        filepath = filepath.replace("\\", "/");
//        File word = new File(filepath);
//        if (word.getPath().endsWith("docx")) {
//            content.append(docxContent(word));
//        }else if(word.getPath().endsWith("doc")){
//            content.append(wordContent(word));
//        }
//        content.append("\r\n");
////        writeContent(content.toString(), response);
//        return content.toString();
//    }

//    private static String wordContent(File word) {
//        String content = "";
//        try {
//            FileInputStream fis = new FileInputStream(word);
//            HWPFDocument doc = new HWPFDocument(fis);
//
//            Document document = DocumentBuilderFactory.newInstance()
//                    .newDocumentBuilder().newDocument();
//            WordToHtmlConverter wordToHtmlConverter = new WordToHtmlConverter(document);
//            wordToHtmlConverter.processDocument(doc);
//            Document htmlDocument = wordToHtmlConverter.getDocument();
//            ByteArrayOutputStream outStream = new ByteArrayOutputStream();
//            DOMSource domSource = new DOMSource(htmlDocument);
//            StreamResult streamResult = new StreamResult(outStream);
//            TransformerFactory tf = TransformerFactory.newInstance();
//            Transformer serializer = tf.newTransformer();
//            serializer.setOutputProperty(OutputKeys.ENCODING, "utf-8");
//            serializer.setOutputProperty(OutputKeys.INDENT, "yes");
//            serializer.setOutputProperty(OutputKeys.METHOD, "html");
//            serializer.transform(domSource, streamResult);
//            outStream.close();
//            content = outStream.toString("UTF-8");
//            fis.close();
//        } catch (Exception e) {
//            logger.error("error",e);
//        }
//        return content;
//    }

    public static void writeContent(String content, HttpServletResponse response) {
        PrintWriter writer = null;
        try {
            response.setContentType("text/html;charset=utf-8");
            writer = response.getWriter();
            writer.write(content);
        } catch (IOException e) {
            log.error("error",e);
        } finally {
            if (!Objects.isNull(writer)) {
                writer.close();
            }
        }
    }

//    private static String docxContent(File word) {
//        String content = "";
//        try {
//            FileInputStream fis = new FileInputStream(word);
//            XWPFDocument docxDocument = new XWPFDocument(fis);
//            // 配置
//            XHTMLOptions options = XHTMLOptions.create();
//            // 设置图片存储路径
//            String path = System.getProperty("java.io.tmpdir");
//            String firstImagePathStr = path + "/" + System.currentTimeMillis();
//            options.setExtractor(new FileImageExtractor(new File(firstImagePathStr)));
//            options.URIResolver(new BasicURIResolver(firstImagePathStr));
//            // 转换html
//            ByteArrayOutputStream htmlStream = new ByteArrayOutputStream();
//            XHTMLConverter.getInstance().convert(docxDocument, htmlStream, options);
//            content = htmlStream.toString();
//            // 将image文件转换为base64并替换到html字符串里
//            String middleImageDirStr = "/word/media";
//            String imageDirStr = firstImagePathStr + middleImageDirStr;
//            File imageDir = new File(imageDirStr);
//            String[] imageList = imageDir.list();
//            if (imageList != null) {
//                for (int i = 0; i < imageList.length; i++) {
//                    String oneImagePathStr = imageDirStr + "/" + imageList[i];
//                    File oneImageFile = new File(oneImagePathStr);
//                    org.apache.commons.io.FileUtils utils= new org.apache.commons.io.FileUtils();
//                    String imageBase64Str = new String(
//                            Base64.encodeBase64(
//                                    utils.readFileToByteArray(oneImageFile)), "UTF-8");
//                    content = content.replace(oneImagePathStr, "data:image/png;base64," + imageBase64Str);
//                }
//            }
//            //删除图片路径
//            File firstImagePath = new File(firstImagePathStr);
//            FileUtils.deleteDirectory(firstImagePath);
//            return content;
//
//        } catch (Exception e) {
//            logger.error("error",e);
//        }
//
//        return content;
//    }

}
